package com.cskaoyan.batch;

import com.cskaoyan.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/10/08 16:20
 *
 * @author ciggar
 */
public class BatchDemo {

    static Connection connection;
    static {
        connection = JDBCUtils.getConnection();
    }


    public static void main(String[] args) throws SQLException {

        int num = 100000;

//        long t1 = System.currentTimeMillis();
//        foreachInsert(num);
//        long t2 = System.currentTimeMillis();
//        statementBatch(num);
        long t3 = System.currentTimeMillis();
        preparedStatementBatch(num);
        long t4 = System.currentTimeMillis();


//        System.out.println("foreach用时：" + (t2-t1) + " ms");
//        System.out.println("statement用时：" + (t3-t2) + " ms");
        System.out.println("prepareStatement用时：" + (t4-t3) + " ms");


    }


    // for循环
    public static void foreachInsert(int num) throws SQLException {


        for (int i = 0; i < num; i++) {

            Statement statement = connection.createStatement();

            String name = "foreach:"  + i;
            statement.executeUpdate("insert into user values (null,'"+name+"',null)");

            statement.close();
        }

    }


    // statement批处理  通信一次
    public static void statementBatch(int num) throws SQLException {

        // 创建statement
        Statement statement = connection.createStatement();


        // 往statement中添加sql语句，内存操作
        for (int i = 0; i < num; i++) {
            String name = "statementBatch:" + i;
            String sql = "insert into user  values (null,'"+name+"',null)";
            statement.addBatch(sql);

        }

        // 把statement中的所有的sql语句都发送给MySQL服务器
        statement.executeBatch();

    }


    // preparedStatement  通信两次
    public static void preparedStatementBatch(int num) throws SQLException {

        // 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement("insert into user values (null,?,null)");


        // 循环去添加参数
        for (int i = 0; i < num; i++) {

            preparedStatement.setString(1,"preparedStatement:"+ i);
            preparedStatement.addBatch();
        }

        // 发送参数并且执行sql语句
        preparedStatement.executeBatch();

    }
}
